package dao;

import gui.PublicMethod;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import service.IService_Dorm;
import service.ServiceImp_Dorm;

import model.Class_NIIT;
import model.CommonList;
import model.Dorm;
import model.Student;

public class DaoImp_Student implements IDao_Student
{
	private DaoMethod method;
	private Connection conn;
	private CommonList common;

	public DaoImp_Student()
	{
		common = CommonList.getInstance();
		method = new DaoMethod();
		conn = DaoMethod.getConnection();
	}

	public List<Student> queryAll(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByClassroom(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where classroom like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where classroom like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where classroom like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByCompany(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where company like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where company like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where company like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByDate(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where date_in like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where date_in like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where date_in like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByMajor(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where major_type like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where major_type like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where major_type like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByName(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where name like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where name like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where name like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryBydormNumber(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where dormNumber like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where dormNumber like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where dormNumber like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByRemark(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where remark like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where remark like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where remark like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryBySchool(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where school like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where school like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where school like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByTeacher(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where teacher like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where teacher like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where teacher like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByTelephone(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where telephone like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where telephone like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where telephone like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByDebt(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where debt like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where debt like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where debt like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryByIsOut(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where isout like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where isout like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where isout like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	public List<Student> queryBySex(String s, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where sex like '%"
					+ s + "%' and isout=0";
			break;
		case PublicMethod.out:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where sex like '%"
					+ s + "%' and isout=1";
			break;
		case PublicMethod.both:
			exe = "select id,name,sex,date_in,dormNumber,classroom,teacher,major_type,school,telephone,company,remark,isout,debt from student_view where sex like '%"
					+ s + "%'";
			break;
		default:
			break;
		}
		return getStudentList(exe);
	}

	private List<Student> getStudentList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					Student student = new Student();
					student.setId(rs.getInt(1));
					student.setName(rs.getString(2));
					student.setSex(rs.getString(3));
					student.setDate_In(rs.getDate(4));
					student.setDormNumber(rs.getString(5));
					student.setClassroom(rs.getString(6));
					student.setTeacher(rs.getString(7));
					student.setMajor_type(rs.getString(8));
					student.setSchool(rs.getString(9));
					student.setTelephone(rs.getString(10));
					student.setCompany(rs.getString(11));
					student.setRemark(rs.getString(12));
					student.setIsout(rs.getInt(13));
					student.setDebt(rs.getInt(14));
					list.add(student);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}

	public int transition(int studentId, int dorm_id)
	{
		String exe = "update student set dorm_id=" + dorm_id + " where id="
				+ studentId;
		return method.stUpdate(exe);
	}

	public int transition_dorm(int dormId, Date date, int newPeopleCount)
	{
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(dormId);
		param.add(date);
		param.add(newPeopleCount);
		String exe = "insert into transition_dorm(dorm_id,date_transition,new_people_count)values(?,?,?)";
		return method.pstUpdate(exe, param);
	}

	public int transition_student(int studentId, Date date, int dorm_id)
	{
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(studentId);
		param.add(date);
		param.add(dorm_id);
		String exe = "insert into transition_student(student_id,date_transition,new_dorm_id)values(?,?,?)";
		return method.pstUpdate(exe, param);
	}

	public int updateInfo(Student student1, Student student2)
	{

		String exe = "update student set telephone=?,class_id=?,school=?,company=?,remark=? where name=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(student2.getTelephone());
		param.add(common.getClassroom_class().get(student2.getClassroom()));
		param.add(student2.getSchool());
		param.add(student2.getCompany());
		param.add(student2.getRemark());
		param.add(student1.getName());
		return method.pstUpdate(exe, param);
	}

}
